from common.connect_db import MyDB

my_db = MyDB()


class DataBaseThird:
    """
    投资
    """

    @staticmethod
    def filter_life_investment_phases_by_phase_id(phase_id_list):
        """
        获得准备分红的订单信息
        :param phase_id_list:
        :return:

        """
        t = str(phase_id_list).replace("[", '(').replace("]", ')')
        sql = "select * from life_investment_phases " \
              "where id in %s " % t
        result = my_db.execute_select('third', sql)
        if len(result) > 0:
            return result
        else:
            raise ValueError("没有相关订单！")

    @staticmethod
    def filter_life_investment_phase_dividends_by_id(investment_phase_id, member_id):
        """
        获得当前投资计划
        :param investment_phase_id: 投资订单表的ID
        :param member_id:
        :return:
        """
        sql = "select * from life_investment_phase_dividends " \
              "where id = '%s' and member_id = '%s' " % (investment_phase_id, member_id)
        result = my_db.execute_select('third', sql)
        if len(result) == 1:
            return result[0]
        else:
            raise ValueError("没有相关信息！")

    @staticmethod
    def filter_life_investment_projects_by_id(project_id):
        """
        获得投资项目的详情
        :param project_id: 投资订单表的ID
        :return:
        """
        sql = "select * from life_investment_projects " \
              "where id = '%s'" % project_id
        result = my_db.execute_select('third', sql)
        if len(result) == 1:
            return result[0]
        else:
            raise ValueError("没有相关信息！")

    @staticmethod
    def filter_life_investment_phase_dividends_by_member_id(member_id, now_time):
        """
        获得某一用户所有待分红的记录
        :param member_id:
        :param now_time:
        :return:
        """
        sql = "select * from life_investment_phase_dividends " \
              "where member_id = '%s' and dividend_time <= '%s' and status =1 order by created_at" % (
                  member_id, now_time)
        result = my_db.execute_select('third', sql)
        if len(result) > 0:
            return result
        else:
            raise ValueError("没有相关信息！")

    @staticmethod
    def filter_life_investment_project_orders_refund_order_by_member_id(member_id):
        """
        查询所有可退款订单信息
        :param member_id:
        :return:
        """
        sql = "select * from life_investment_project_orders " \
              "where member_id = '%s' and status =2 order by created_at" % member_id
        result = my_db.execute_select('third', sql)
        if len(result) > 0:
            return result
        else:
            raise ValueError("没有相关信息！")

    @staticmethod
    def filter_life_investment_project_orders_by_id(order_id):
        """
        根据投资订单ID获得退款ID
        :param order_id:
        :return:

        """
        sql = "select * from life_investment_project_orders where id = %s " % order_id
        result = my_db.execute_select('third', sql)
        if len(result) == 1:
            return result[0]
        else:
            raise ValueError("没有相关订单！")

    @staticmethod
    def assert_life_investment_project_orders_status(order_id, status):
        """
        断言订单表的状态
        :param order_id:
        :param status:
        :return:
        """
        sql = "select * from life_investment_project_orders " \
              "where id = %s and status = '%s'" % (order_id, status)
        result = my_db.execute_select('third', sql)
        if len(result) == 1:
            return True
        else:
            raise False

    @staticmethod
    def assert_life_investment_refunds_status(refund_id, status):
        """
        断言退款表的的状态
        :param refund_id:
        :param status:
        :return:
        """
        sql = "select * from life_investment_refunds " \
              "where id = %s and status = '%s'" % (refund_id, status)
        result = my_db.execute_select('third', sql)
        if len(result) == 1:
            return True
        else:
            raise False

    @staticmethod
    def assert_life_investment_phase_dividends(phase_dividends_id, investment_phase_id, member_id, invest_amount,
                                               dividend_ratio,
                                               dividend_amount, dividend_given_time, status):
        """
        断言life_investment_phase_dividends表
        :param phase_dividends_id:
        :param investment_phase_id:
        :param member_id:
        :param invest_amount:
        :param dividend_ratio:
        :param dividend_amount:
        :param dividend_given_time:
        :param status:分红状态：1待分红 2已分红 -1终止
        :return:True or False
        """
        sql = "select * from life_investment_phase_dividends " \
              "where id = '%s' and investment_phase_id ='%s' and member_id ='%s' " \
              " and invest_amount ='%s' and dividend_ratio ='%s' " \
              "and dividend_amount ='%s'  and status ='%s' " \
              "" % (phase_dividends_id, investment_phase_id, member_id, invest_amount, dividend_ratio,
                    dividend_amount, status)
        result = my_db.execute_select('third', sql)
        if len(result) == 1:
            return True
        else:
            return False

    @staticmethod
    def assert_life_investment_phase(phase_id, project_id, member_id, invest_amount, dividend_amount):
        """
        断言life_investment_phases表
        :param phase_id:
        :param project_id:
        :param member_id:
        :param invest_amount:
        :param dividend_amount:
        :return: True or False
        """
        sql = "select * from life_investment_phases " \
              "where id = '%s' and project_id ='%s' and member_id ='%s' " \
              "and invest_amount ='%s' and dividend_amount ='%s' " \
              "" % (phase_id, project_id, member_id, invest_amount, dividend_amount)
        result = my_db.execute_select('third', sql)
        if len(result) == 1:
            return True
        else:
            return False

    """
    酒店
    """

    @staticmethod
    def filter_life_third_hotel_orders_by_member_id(member_id):
        """
        通过手机号获取该用户的最后一笔支付成功的订单
        笔数：1  最后一笔
        条件：支付成功
        :param member_id:
        :return: 返回对象
        """
        sql = "select * from life_third_hotel_orders " \
              "where member_id = '%s' and status in (1,2,3) order by created_at desc limit 1" % member_id
        result = my_db.execute_select('third', sql)
        if len(result) == 1:
            return result[0]
        else:
            raise ValueError("没有相关订单！")

    @staticmethod
    def get_life_third_hotels(limit=99999):
        """
        获得所有酒店
        :param limit:
        :return:
        """
        sql = "select * from life_third_hotels order by rand() limit %s" % limit
        result = my_db.execute_select('third', sql)
        if len(result) > 0:
            return result
        else:
            raise ValueError("没有酒店数据！")

    """
    机票
    """

    @staticmethod
    def filter_life_third_air_orders_by_member_id(member_id):
        """
        通过手机号获取该用户的最后一笔支付成功的订单
        笔数：1  最后一笔
        条件：支付成功
        :param member_id:
        :return: 返回对象
        """
        sql = "select * from life_third_air_orders " \
              "where member_id = '%s' order by id desc limit 1" % member_id
        result = my_db.execute_select('third', sql)
        if len(result) == 1:
            return result[0]
        else:
            raise ValueError("没有相关订单！")
